Configuring JDBC Data Sources

Using JDBC data sources provides flexibility to make environment changes and reduces the time it takes to reconfigure your infrastructure when a change is made. For example, if a SequeLink service is reconfigured (for example, moved to another machine, port, and so on), the SequeLink administrator can change and run the configuration source file described in "Creating and Managing JDBC Data Sources", reassigning the logical name of the JDBC data source to the changed data source configuration. As a result, the client application code does not have to change, because it only refers to the logical name of the JDBC data source.

SequeLink supports the following JDBC data source implementations defined by the JDBC 2.0 Optional Package:

JNDI for Naming Databases
Connection pooling
Distributed Transaction Management Support

NOTES:

You must include the javax.sql.* and javax.naming.* classes to create and use JDBC data sources. The JDBC Client provides all the necessary JAR files that contain the required classes and interfaces.
In addition, you must include the javax.transaction.xa.* class to use and implement distributed transactions.

Creating and Managing JDBC Data Sources

JDBC data sources are implemented using a SequeLink class com.ddtek.sequelink.jdbcx.datasource.SequeLinkDataSource. This single data source implementation implements the following interfaces defined in the JDBC 2.0 Optional Package:

javax.sql.DataSource
javax.sql.ConnectionPoolDataSource
javax.sql.XADataSource

The SequeLink Data Source implementation implements both the java.io.Serializable and javax.naming.Referenceable interfaces. The interface that is used depends on the service provider you are using and how the SequeLinkDataSource object is saved in your JNDI environment.

Your JDBC Client installation contains the following examples that show how to create and use JDBC data sources:

JNDI_LDAP_Example.java. Use this example to create a JDBC data source and save it in your LDAP directory, using the JNDI Provider for LDAP.
JNDI_FILESYSTEM_Example.java. Use this example to create a JDBC data source and save it in your local file system, using the File System JNDI Provider.

NOTE: You must include the javax.sql.* and javax.naming.* classes to create and use SequeLink JDBC data sources. The SequeLink JDBC driver provides all the necessary JAR files, which contain the required classes and interfaces.

Calling a Data Source in an Application

Applications can call a SequeLink JDBC data source using a logical name to retrieve the javax.sql.DataSource object. This object loads the specified driver and can be used to establish a connection to the database.

Once the data source has been registered with JNDI, it can be used by your JDBC application as shown in the following example:

Context ctx = new InitialContext(); 
DataSource ds = (DataSource)ctx.lookup("EmployeeDB"); 
Connection con = ds.getConnection("scott", "tiger"); 

In this example, the JNDI environment is first initialized. Next, the initial naming context is used to find the logical name of the data source (EmployeeDB). The Context.lookup() method returns a reference to a Java object, which is narrowed to a javax.sql.DataSource object. Finally, the DataSource.getConnection() method is called to establish a connection with the database.

See "Creating and Managing JDBC Data Sources" for information about example data sources shipped with SequeLink Client for JDBC that you can use as a template for creating your own data sources.

Using JNDI for Naming Databases

Instead of using connection URLs, client applications can access a JNDI-named data source using a logical name to retrieve the javax.sql.DataSource object. This object loads the JDBC driver and establishes the connection to the SequeLink service.

Once a JDBC data source has been registered with JNDI, it can be used by your JDBC application as shown in the following example:

Context ctx = new InitialContext(); 
DataSource ds = (DataSource)ctx.lookup("jdbc/EmployeeDB"); 
Connection con = ds.getConnection("scott", "tiger"); 

In this example, the JNDI environment is first initialized. Next, the initial naming context is used to find the logical name of the JDBC data source. The Context.lookup() method returns a reference to a Java object, which is narrowed to a javax.sql.DataSource object. Finally, the DataSource.getConnection() method is called to establish a connection with the SequeLink service.

See "Creating and Managing JDBC Data Sources" for instructions on creating JDBC data sources.

Using Connection Pooling

Connection pooling allows you to reuse connections rather than create a new one every time the SequeLink Client needs to establish a data access connection. Connection pooling manages connection sharing across different user requests to maintain performance and reduce the number of new connections that must be created. For example, compare the transaction sequences shown in "Example A: Without Connection Pooling" and "Example B: With Connection Pooling".

Example A: Without Connection Pooling

The client application creates a connection.
The client application sends a data access query.
The client application obtains the result set of the query.
The client application displays the result set to the end user.
The client application ends the connection.

Example B: With Connection Pooling

The client checks the connection pool for an unused connection.
If an unused connection exists, it is returned by the pool implementation; otherwise, it creates a new connection.
The client application sends a data access query.
The client application obtains the result set of the query.
The client application displays the result set to the end user.
The client application returns the connection to the pool.

NOTE: The client application still calls close(), but the connection remains open and the pool is notified of the close request.

The pool implementation creates real database connections using the getPooledConnection() method of ConnectionPoolDataSource. Then, the pool implementation registers itself as a listener to the PooledConnection. When a client application requests a connection, the pool implementation is notified by the ConnectionEventListener interface that the connection is free and available for reuse. The pool implementation is also notified by the ConnectionEventListener interface when the client somehow corrupts the database connection, so that the pool implementation can remove that connection from the pool.

Once a JDBC data source has been registered with JNDI, it can be used by your JDBC application as shown in the following example, typically through a third-party connection pool tool:

Context ctx = new InitialContext(); 
ConnectionPoolDataSource ds = (ConnectionPoolDataSource)
ctx.lookup("jdbc/EmployeeDB"); 
pooledConnection pcon = ds.getPooledConnection("scott", 
"tiger"); 

In this example, the JNDI environment is first initialized. Next, the initial naming context is used to find the logical name of the JDBC data source. The Context.lookup() method returns a reference to a Java object, which is narrowed to a javax.sql.ConnectionPoolDataSource object. Finally, the ConnectionPoolDataSource.getPooledConnection() method is called to establish a connection with the SequeLink service.

See "Creating and Managing JDBC Data Sources" for instructions on creating JDBC data sources. See "JDBC Connection Pool Manager" for more information on the DataDirect Connection Pool Manager.